package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.model.po.WorkOrder;
import com.example.demo.model.vo.WorkOrderVo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface WorkOrderMapper extends BaseMapper<WorkOrder> {

    @Select("select * from work_order where order_no = #{orderNo}")
    List<WorkOrder> findAll(Long orderNo);

    @Select("SELECT    \n" +
            "    d.dept_id,    \n" +
            "    d.dept_name,    \n" +
            "    COUNT(wo.id) AS total_orders,    \n" +
            "    SUM(CASE WHEN wo.is_overdue = 1 THEN 1 ELSE 0 END) AS overdue_orders,    \n" +
            "    ROUND(SUM(CASE WHEN wo.is_overdue = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(wo.id), 2) AS overdue_rate    \n" +
            "FROM    \n" +
            "    dept d    \n" +
            "LEFT JOIN    \n" +
            "    work_order wo ON d.dept_id = wo.handle_dept_id    \n" +
            "WHERE    \n" +
            "    wo.create_time BETWEEN UNIX_TIMESTAMP('2024-07-01 00:00:00') * 1000 AND UNIX_TIMESTAMP('2024-07-31 23:59:59') * 1000    \n" +
            "GROUP BY    \n" +
            "    d.dept_id, d.dept_name    \n" +
            "ORDER BY    \n" +
            "    d.dept_name;")
    @Results({
            @Result(property = "deptId", column = "dept_id"),
            @Result(property = "deptName", column = "dept_name"),
            @Result(property = "totalOrders", column = "total_orders"),
            @Result(property = "overdueOrders", column = "overdue_orders"),
            @Result(property = "overdueRate", column = "overdue_rate")
    })
    List<WorkOrderVo> selectDepartmentStatsForJuly();

    @Select("SELECT    \n" +
            "    wo.order_type,    \n" +
            "    COUNT(wo.id) AS total_orders,    \n" +
            "    SUM(CASE WHEN wo.is_overdue = 1 THEN 1 ELSE 0 END) AS overdue_orders,    \n" +
            "    ROUND(SUM(CASE WHEN wo.is_overdue = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(wo.id), 2) AS overdue_rate    \n" +
            "FROM    \n" +
            "    work_order wo    \n" +
            "WHERE    \n" +
            "     wo.create_time BETWEEN UNIX_TIMESTAMP('2024-07-01 00:00:00') * 1000 AND UNIX_TIMESTAMP('2024-07-31 23:59:59') * 1000     \n" +
            "GROUP BY    \n" +
            "    wo.order_type    \n" +
            "ORDER BY    \n" +
            "    wo.order_type;")
    @Results({
            @Result(property = "orderType", column = "order_type"),
            @Result(property = "totalOrders", column = "total_orders"),
            @Result(property = "overdueOrders", column = "overdue_orders"),
            @Result(property = "overdueRate", column = "overdue_rate")
    })
    List<WorkOrderVo> selectOrderTypeStatsForJuly();
}
